# -*- coding:utf8 -*- #

# -----------------------------------------------------------------------------------

# ProjectName:   property-ui-auto-test

# FileName:     read_excel

# Author:      

# Datetime:    2022/5/2 16:08

# Description：

# -----------------------------------------------------------------------------------
import openpyxl


def read_excel(start, end, sheetname, filename=r"C:\Users\Administrator\Desktop\电表.xlsx"):
    # 根据filename路径打开Excel文件
    wb = openpyxl.load_workbook(filename)
    # 根据sheetname选择表格页进行操作
    ws = wb[sheetname]
    # 定义一个空的列表
    all_list = []
    for hang in ws:
        # 每一行定义一个列表
        hang_list = []
        for i in hang:
            # 如果单元格值为None
            if i.value is None:
                # 把None赋值为空字符串
                i.value = ""
            #  遍历出来的i值添加到列表
            hang_list.append(i.value)
        # 把hang列表添加到空列表中,形成列表套列表的格式
        # hang_list = hang_list[1::]
        all_list.append(hang_list)
        # 返回列表切片后第一个开始的列表数据
    list_first = []
    for k in all_list:
        list_first.append(k[0])
    list_first1 = list_first[1::]
    list_first2 = []
    for v in list_first1:
        if type(v) == str:
            if v.isdigit():
                list_first2.append(int(v))
            else:
                list_first2.append(v)
    return list_first2[start:end:]


# 调试代码入口
if __name__ == '__main__':
    # 打印文件内容
    list1 = ["NB-lot单相电能表", "NB-lot三相二次表", "NB-lot三相一次表", "Rs单相电表", "Rs三相二次电表", "Rs三相一次电表", "基站"]
    for i in list1:
        if i == "基站":
            print("{}:{}".format(i, str(read_excel(0, 100, i)).replace("'", "")))
        else:
            print("{}:{}".format(i, read_excel(0, 100, i)))
